﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class IntransitDA : IIntransitDA
    {
        private string ACTUAL_SHIP_DATE = "@ACTUAL_SHIP_DATE";
        private string CREATED_BY = "@CREATED_BY";
        private string DEST_WH_DB_ID = "@DEST_WH_DB_ID";
        private string EXPECTED_RECEIPT_DATE = "@EXPECTED_RECEIPT_DATE";
        private string INTRANSIT_ID = "@INTRANSIT_ID";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string ORDER_DATE = "@ORDER_DATE";
        private string ORDER_ID = "@ORDER_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_INTRANSIT_ID_UNIQUE = " SELECT COUNT(1) FROM @_@INTRANSIT WHERE INTRANSIT_ID = @INTRANSIT_ID ";
        private string SQL_DELETE_INTRANSIT = " DELETE FROM @_@INTRANSIT WHERE INTRANSIT_ID = @INTRANSIT_ID ";
        private string SQL_DELETE_INTRANSIT_BY_ORDER_ID = " DELETE FROM @_@INTRANSIT WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_INSERT_INTRANSIT = " INSERT INTO @_@INTRANSIT ( WH_ID, INTRANSIT_ID, SRC_WH_DB_ID, DEST_WH_DB_ID, OWNER_ID, ORDER_ID, RECEIPT_ID, ORDER_DATE, ACTUAL_SHIP_DATE, EXPECTED_RECEIPT_DATE, STATUS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @INTRANSIT_ID, @SRC_WH_DB_ID, @DEST_WH_DB_ID, @OWNER_ID, @ORDER_ID, @RECEIPT_ID, @ORDER_DATE, @ACTUAL_SHIP_DATE, @EXPECTED_RECEIPT_DATE, @STATUS, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_INTRANSITS = " SELECT  ROWID, WH_ID, INTRANSIT_ID, SRC_WH_DB_ID, DEST_WH_DB_ID, OWNER_ID, ORDER_ID, RECEIPT_ID, ORDER_DATE, ACTUAL_SHIP_DATE, EXPECTED_RECEIPT_DATE, STATUS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@INTRANSIT ";
        private string SQL_UPDATE_INTRANSIT = " UPDATE @_@INTRANSIT SET WH_ID = @WH_ID, SRC_WH_DB_ID = @SRC_WH_DB_ID, DEST_WH_DB_ID = @DEST_WH_DB_ID, OWNER_ID = @OWNER_ID, ORDER_ID = @ORDER_ID, RECEIPT_ID = @RECEIPT_ID, ORDER_DATE = @ORDER_DATE, ACTUAL_SHIP_DATE = @ACTUAL_SHIP_DATE, EXPECTED_RECEIPT_DATE = @EXPECTED_RECEIPT_DATE, STATUS = @STATUS, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE INTRANSIT_ID = @INTRANSIT_ID  ";
        private string SQL_UPDATE_INTRANSIT_STATUS = " UPDATE @_@INTRANSIT SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE INTRANSIT_ID = @INTRANSIT_ID  ";
        private string SQL_UPDATE_INTRANSIT_STATUS_AND_ASD = " UPDATE @_@INTRANSIT SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),ACTUAL_SHIP_DATE = GETDATE() WHERE INTRANSIT_ID = @INTRANSIT_ID  ";
        private string SQL_UPDATE_RECEIPT_ID = " UPDATE @_@INTRANSIT SET RECEIPT_ID = @RECEIPT_ID, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE INTRANSIT_ID = @INTRANSIT_ID  ";
        private string SRC_WH_DB_ID = "@SRC_WH_DB_ID";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckIntransitIDUnique(string intransitID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_INTRANSIT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckIntransitIDUnique(DataBase dataBase, DbTransaction tran, string intransitID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_INTRANSIT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteIntransit(List<string> intransitID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < intransitID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INTRANSIT.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteIntransit(DataBase dataBase, DbTransaction tran, List<string> intransitID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < intransitID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INTRANSIT.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteIntransitByOrderID(List<string> orderIDs, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < orderIDs.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderIDs[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INTRANSIT_BY_ORDER_ID.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteIntransitByOrderID(DataBase dataBase, DbTransaction tran, List<string> orderIDs, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < orderIDs.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderIDs[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INTRANSIT_BY_ORDER_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllIntransit(List<string> allWhDbIDs)
        {
            string sql = this.GetSQL_SELECT_ALL_INTRANSIT(allWhDbIDs);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllIntransit(DataBase dataBase, DbTransaction tran, List<string> allWhDbIDs)
        {
            string sql = this.GetSQL_SELECT_ALL_INTRANSIT(allWhDbIDs);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public IntransitInfo GetIntransitByID(string intransitID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INTRANSITS.Replace("@_@", whLoginID) + " WHERE INTRANSIT_ID = @INTRANSIT_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID) };
            IntransitInfo intransitInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    intransitInfo = new IntransitInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), (reader["SRC_WH_DB_ID"].ToString() != "") ? int.Parse(reader["SRC_WH_DB_ID"].ToString()) : 0, (reader["DEST_WH_DB_ID"].ToString() != "") ? int.Parse(reader["DEST_WH_DB_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return intransitInfo;
        }

        public IntransitInfo GetIntransitByID(string intransitID, string srcWhDbID, List<string> allWhDbIDs)
        {
            string sql = this.GetSQL_SELECT_ALL_INTRANSIT(allWhDbIDs) + " WHERE INTRANSIT_ID = @INTRANSIT_ID AND SRC_WH_DB_ID = @SRC_WH_DB_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID), new SqlParameter("@SRC_WH_DB_ID", srcWhDbID) };
            IntransitInfo intransitInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    intransitInfo = new IntransitInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), (reader["SRC_WH_DB_ID"].ToString() != "") ? int.Parse(reader["SRC_WH_DB_ID"].ToString()) : 0, (reader["DEST_WH_DB_ID"].ToString() != "") ? int.Parse(reader["DEST_WH_DB_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return intransitInfo;
        }

        public IntransitInfo GetIntransitByID(DataBase dataBase, DbTransaction tran, string intransitID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INTRANSITS.Replace("@_@", whLoginID) + " WHERE INTRANSIT_ID = @INTRANSIT_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID) };
            IntransitInfo intransitInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                intransitInfo = new IntransitInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), (reader["SRC_WH_DB_ID"].ToString() != "") ? int.Parse(reader["SRC_WH_DB_ID"].ToString()) : 0, (reader["DEST_WH_DB_ID"].ToString() != "") ? int.Parse(reader["DEST_WH_DB_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return intransitInfo;
        }

        public IntransitInfo GetIntransitByID(DataBase dataBase, DbTransaction tran, string intransitID, string srcWhDbID, List<string> allWhDbIDs)
        {
            string sql = this.GetSQL_SELECT_ALL_INTRANSIT(allWhDbIDs) + " WHERE INTRANSIT_ID = @INTRANSIT_ID AND SRC_WH_DB_ID = @SRC_WH_DB_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID), new SqlParameter("@SRC_WH_DB_ID", srcWhDbID) };
            IntransitInfo intransitInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                intransitInfo = new IntransitInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), (reader["SRC_WH_DB_ID"].ToString() != "") ? int.Parse(reader["SRC_WH_DB_ID"].ToString()) : 0, (reader["DEST_WH_DB_ID"].ToString() != "") ? int.Parse(reader["DEST_WH_DB_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return intransitInfo;
        }

        public IntransitInfo GetIntransitByOrderID(string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INTRANSITS.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            IntransitInfo intransitInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    intransitInfo = new IntransitInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), (reader["SRC_WH_DB_ID"].ToString() != "") ? int.Parse(reader["SRC_WH_DB_ID"].ToString()) : 0, (reader["DEST_WH_DB_ID"].ToString() != "") ? int.Parse(reader["DEST_WH_DB_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return intransitInfo;
        }

        public IntransitInfo GetIntransitByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INTRANSITS.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            IntransitInfo intransitInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                intransitInfo = new IntransitInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), (reader["SRC_WH_DB_ID"].ToString() != "") ? int.Parse(reader["SRC_WH_DB_ID"].ToString()) : 0, (reader["DEST_WH_DB_ID"].ToString() != "") ? int.Parse(reader["DEST_WH_DB_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return intransitInfo;
        }

        public DataSet GetIntransitByQueryList(List<string> sqlWhere, IntransitQueryEntity intransitQuery, List<string> allWhDbIDs)
        {
            string temp = this.GetSQL_SELECT_ALL_INTRANSIT(allWhDbIDs);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!intransitQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, intransitQuery.CurrentPage, intransitQuery.PageSize, intransitQuery.SortField, intransitQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetIntransitByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, IntransitQueryEntity intransitQuery, List<string> allWhDbIDs)
        {
            string temp = this.GetSQL_SELECT_ALL_INTRANSIT(allWhDbIDs);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!intransitQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, intransitQuery.CurrentPage, intransitQuery.PageSize, intransitQuery.SortField, intransitQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public string GetSQL_SELECT_ALL_INTRANSIT(List<string> allWhDbIDs)
        {
            string sqlTemp = " SELECT  ROWID, WH_ID, INTRANSIT_ID, SRC_WH_DB_ID, DEST_WH_DB_ID, OWNER_ID, ORDER_ID, RECEIPT_ID, ORDER_DATE, ACTUAL_SHIP_DATE, EXPECTED_RECEIPT_DATE, STATUS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@INTRANSIT ";
            string SQL_SELECT_ALL_INTRANSIT = " SELECT DB.WH_DB_DESC AS SRCDBDESC,DB2.WH_DB_DESC AS DESTDBDESC , * FROM ( ";
            if ((allWhDbIDs != null) && (allWhDbIDs.Count > 0))
            {
                for (int i = 0; i < allWhDbIDs.Count; i++)
                {
                    SQL_SELECT_ALL_INTRANSIT = (SQL_SELECT_ALL_INTRANSIT + sqlTemp + " union all ").Replace("@_@", allWhDbIDs[i]);
                }
            }
            if (SQL_SELECT_ALL_INTRANSIT.EndsWith(" union all "))
            {
                SQL_SELECT_ALL_INTRANSIT = SQL_SELECT_ALL_INTRANSIT.Substring(0, SQL_SELECT_ALL_INTRANSIT.LastIndexOf("union all"));
            }
            return (SQL_SELECT_ALL_INTRANSIT + " ) temp  LEFT JOIN SYSADM.DB_CONFIG DB ON SRC_WH_DB_ID = DB.WH_DB_ID  LEFT JOIN SYSADM.DB_CONFIG DB2 ON DEST_WH_DB_ID = DB2.WH_DB_ID ");
        }

        public int InsertIntransit(IntransitInfo intransitInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    intransitInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Intransit_Parameters(intransitInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INTRANSIT.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertIntransit(DataBase dataBase, DbTransaction tran, IntransitInfo intransitInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    intransitInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Intransit_Parameters(intransitInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INTRANSIT.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Intransit_Parameters(IntransitInfo intransitInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.INTRANSIT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SRC_WH_DB_ID, SqlDbType.Int, 4), new SqlParameter(this.DEST_WH_DB_ID, SqlDbType.Int, 4), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.ACTUAL_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPECTED_RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x200), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(intransitInfo.WhID))
            {
                paramArray[0].Value = intransitInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = intransitInfo.IntransitID;
            paramArray[2].Value = intransitInfo.SrcWhDbID;
            paramArray[3].Value = intransitInfo.DestWhDbID;
            paramArray[4].Value = intransitInfo.OwnerID;
            paramArray[5].Value = intransitInfo.OrderID;
            paramArray[6].Value = intransitInfo.ReceiptID;
            paramArray[7].Value = intransitInfo.OrderDate;
            DateTime CS00003 = new DateTime();
            if (intransitInfo.ActualShipDate != CS00003)
            {
                paramArray[8].Value = intransitInfo.ActualShipDate;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (intransitInfo.ExpectedReceiptDate != new DateTime())
            {
                paramArray[9].Value = intransitInfo.ExpectedReceiptDate;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            paramArray[10].Value = intransitInfo.Status;
            if (!string.IsNullOrEmpty(intransitInfo.Udf1))
            {
                paramArray[11].Value = intransitInfo.Udf1;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf2))
            {
                paramArray[12].Value = intransitInfo.Udf2;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf3))
            {
                paramArray[13].Value = intransitInfo.Udf3;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf4))
            {
                paramArray[14].Value = intransitInfo.Udf4;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf5))
            {
                paramArray[15].Value = intransitInfo.Udf5;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf6))
            {
                paramArray[0x10].Value = intransitInfo.Udf6;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf7))
            {
                paramArray[0x11].Value = intransitInfo.Udf7;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf8))
            {
                paramArray[0x12].Value = intransitInfo.Udf8;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf9))
            {
                paramArray[0x13].Value = intransitInfo.Udf9;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Udf10))
            {
                paramArray[20].Value = intransitInfo.Udf10;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(intransitInfo.Remark))
            {
                paramArray[0x15].Value = intransitInfo.Remark;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            paramArray[0x16].Value = intransitInfo.IsActive;
            paramArray[0x17].Value = intransitInfo.CreatedBy;
            paramArray[0x18].Value = intransitInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateIntransit(IntransitInfo intransitInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    intransitInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Intransit_Parameters(intransitInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INTRANSIT.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateIntransit(DataBase dataBase, DbTransaction tran, IntransitInfo intransitInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    intransitInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Intransit_Parameters(intransitInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INTRANSIT.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateIntransitStatus(string intransitID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                string sql = string.Empty;
                if (status == IntransitStatus.InTransit)
                {
                    sql = this.SQL_UPDATE_INTRANSIT_STATUS_AND_ASD;
                }
                else
                {
                    sql = this.SQL_UPDATE_INTRANSIT_STATUS;
                }
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
                result += db.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateIntransitStatus(DataBase dataBase, DbTransaction tran, string intransitID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                if (status == IntransitStatus.InTransit)
                {
                    sql = this.SQL_UPDATE_INTRANSIT_STATUS_AND_ASD;
                }
                else
                {
                    sql = this.SQL_UPDATE_INTRANSIT_STATUS;
                }
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptIDByIntransitID(string intransitID, string receiptID, string updatedBy, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID), new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@UPDATED_BY", updatedBy) };
                result = db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RECEIPT_ID.Replace("@_@", whLoginID), paras);
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateReceiptIDByIntransitID(DataBase dataBase, DbTransaction tran, string intransitID, string receiptID, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTRANSIT_ID", intransitID), new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@UPDATED_BY", updatedBy) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RECEIPT_ID.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

